{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "848523b1-cb88-4fb8-a450-dd26f44c2f6a",
   "metadata": {},
   "source": [
    "# Exercise 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24c1bd7b-a907-49d9-b49b-77be709131bb",
   "metadata": {},
   "source": [
    "## Question 1\n",
    "Load the prices data from SP500.csv to the SQLite database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a5325b0c-f961-486d-9e98-fdd3a808886c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import csv\n",
    "from contextlib import closing\n",
    "\n",
    "conn = sqlite3.connect(\"Exercise3.db\")\n",
    "cs = conn.cursor()\n",
    "cs.execute(\"\"\"\n",
    "create table if not exists prices (\n",
    "theday text primary key,\n",
    "price real\n",
    ");\n",
    "\"\"\")\n",
    "with closing(open('SP500.csv')) as datafile:\n",
    "    # your code goes here\n",
    "conn.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e2fceb7-2289-4404-b1ca-ae95aabfbefa",
   "metadata": {},
   "source": [
    "## Question 2\n",
    "Calibrate a GBM model to the prices of the 120 days prior to *2021-05-31*.\n",
    "\n",
    "**Hint**:\n",
    "1. Read the data from the database\n",
    "2. Calibrate the GBM model in exactly the same way as in Question 3 of Exercise 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fba19d8e-9b6a-4ac1-b911-59e3cfa602d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model parameters:  0.0511,   0.1440\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import sqlite3\n",
    "import csv\n",
    "from contextlib import closing\n",
    "\n",
    "class GBM:\n",
    "    def __init__(self):\n",
    "        self.mu = np.nan;\n",
    "        self.sigma = np.nan;\n",
    "        self.rng = np.random.default_rng()\n",
    "        \n",
    "    def calibrate(self, trajectory, Dt):\n",
    "        # your code goes here\n",
    "\n",
    "test_date = '2021-05-31'\n",
    "N = 120\n",
    "model = GBM();\n",
    "P = None\n",
    "with closing(sqlite3.connect(\"Exercise3.db\")) as conn:\n",
    "    # Your code goes here\n",
    "print(F\"Model parameters: % .4f,  % .4f\" % (model.mu, model.sigma))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75f27938-fd16-4b7c-8018-d78acca1f412",
   "metadata": {},
   "source": [
    "## Question 3\n",
    "Use the calibrated GBM model to forecast the price on *2021-05-31*. What is the 95% confidence interval of the forecast?\n",
    "\n",
    "**Hint**\n",
    "\n",
    "Recall the formula for price forecast:\n",
    "$$S_{t + \\Delta t} = S_t \\exp \\left[\n",
    "\\left(\n",
    "\\mu - \\frac{\\sigma^2}{2}\n",
    "\\right)\\Delta t + \\sigma \\left(\n",
    "W_{t + \\Delta t} - W_t\n",
    "\\right)\n",
    "\\right]\n",
    "$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a31f281d-2e97-4e02-958b-4a9c9e0222d0",
   "metadata": {},
   "source": [
    "Our price forecast is\n",
    "$$\n",
    "\\mathbb E S_{t+\\Delta t} = S_t \\exp \\left(\n",
    "\\mu \\Delta t\n",
    "\\right)\n",
    "$$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "835ba0b8-ad09-4f9f-b45d-0a982c90d8df",
   "metadata": {},
   "source": [
    "$S_t$ is the latest price we have, i.e. the price on *2021-05-30*. $\\Delta t = 1/250$ - make sure you use the same $\\Delta t$ in forecasting as you have done in calibration. $(\\mu - \\frac{\\sigma^2}{2})t + \\sigma(W_{t+\\Delta t} - W_{t})$ has normal distribution with mean $(\\mu - \\frac{\\sigma^2}{2})\\Delta t$ and standard deviation $\\sigma \\sqrt{\\Delta t}$. Use these parameters with scipy.stats.norm.ppf to calculate the 2.5% quantile and the 97.5% quantile of the distribution. Call them $q_1$ and $q_2$."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce053daa-caaf-48eb-8257-473cea175ffe",
   "metadata": {},
   "source": [
    "Then the required confidence interval is $(S_t e^{q_1}, S_t e^{q_2})$."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b2228a6c-d913-40e5-9035-81559dd9e3ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import norm\n",
    "\n",
    "# Your code goes here"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
